﻿using System;
using System.Data;
using System.Data.SQLite;

namespace CommonDB
{
    /// <summary>
    /// 实现sqlite数据库操作类
    /// </summary>
    public class SqliteOperator : IDbOperator
    {
        private IDbProvider _dbProvider;

        public IDbProvider DbProvider
        {
            get { return _dbProvider; }
        }

        public SqliteOperator()
            : this(new SqliteProvider())
        {
        }

        public SqliteOperator(IDbProvider provider)
        {
            _dbProvider = provider;
        }

        #region ExecuteNonQuery 返回受影响行数
        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlString)
        {
            return ExecuteNonQuery(sqlString, false, null);
        }

        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlString, bool isProcedure)
        {
            return ExecuteNonQuery(sqlString, isProcedure, null);
        }

        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="parameters">sql语句对应参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlString, params IDataParameter[] parameters)
        {
            return ExecuteNonQuery(sqlString, false, parameters);
        }

        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程,true 为存储过程</param>
        /// <param name="parameters">sql语句对应参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlString, bool isProcedure, params IDataParameter[] parameters)
        {
            try
            {
                _dbProvider.Connection.Open();
                _dbProvider.Command.CommandText = sqlString;
                _dbProvider.Command.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text;

                _dbProvider.Command.Parameters.Clear();
                if (parameters != null)
                {
                    foreach (IDataParameter param in parameters)
                    {
                        _dbProvider.Command.Parameters.Add(param);
                    }
                }
                int num = _dbProvider.Command.ExecuteNonQuery();
                return num;
            }
            finally
            {
                _dbProvider.Connection.Close();
            }
        }

        /// <summary>
        /// 返回受影响行数(执行事务时使用)
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="parameters">sql语句对应参数</param>
        /// <returns></returns>
        public int TransExecuteNonQuery(string sqlString, params IDataParameter[] parameters)
        {
            return TransExecuteNonQuery(sqlString, false, parameters);
        }

        /// <summary>
        /// 返回受影响行数(执行事务时使用)
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程,true 为存储过程</param>
        /// <param name="parameters">sql语句对应参数</param>
        /// <returns></returns>
        public int TransExecuteNonQuery(string sqlString, bool isProcedure, params IDataParameter[] parameters)
        {
            _dbProvider.Command.CommandText = sqlString;
            _dbProvider.Command.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text;

            _dbProvider.Command.Parameters.Clear();
            if (parameters != null)
            {
                foreach (IDataParameter param in parameters)
                {
                    _dbProvider.Command.Parameters.Add(param);
                }
            }
            int num = _dbProvider.Command.ExecuteNonQuery();
            return num;
        }
        #endregion

        #region ExecuteScalar 返回查询结果的第一行第一列
        /// <summary>
        /// 返回查询结果的第一行第一列
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns></returns>
        public object ExecuteScalar(string sqlString)
        {
            return ExecuteScalar(sqlString, false, null);
        }

        /// <summary>
        /// 返回查询结果的第一行第一列
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否是存储过程</param>
        /// <returns></returns>
        public object ExecuteScalar(string sqlString, bool isProcedure)
        {
            return ExecuteScalar(sqlString, isProcedure, null);
        }

        /// <summary>
        /// 返回查询结果的第一行第一列
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="parameters">sql语句对应输入参数</param>
        /// <returns></returns>
        public object ExecuteScalar(string sqlString, params IDataParameter[] parameters)
        {
            return ExecuteScalar(sqlString, false, parameters);
        }

        /// <summary>
        /// 返回查询结果的第一行第一列
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <param name="parameters">sql语句对应输入参数</param>
        /// <returns></returns>
        public object ExecuteScalar(string sqlString, bool isProcedure, params IDataParameter[] parameters)
        {
            try
            {
                _dbProvider.Connection.Open();
                _dbProvider.Command.CommandText = sqlString;
                _dbProvider.Command.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text;

                _dbProvider.Command.Parameters.Clear();
                if (parameters != null)
                {
                    foreach (IDataParameter param in parameters)
                    {
                        _dbProvider.Command.Parameters.Add(param);
                    }
                }
                object obj = _dbProvider.Command.ExecuteScalar();
                return obj;
            }
            finally
            {
                _dbProvider.Connection.Close();
            }
        }
        #endregion

        #region ExecuteDataReader 返回数据只读游标集
        /// <summary>
        /// 返回数据只读游标集
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns></returns>
        public IDataReader ExecuteDataReader(string sqlString)
        {
            return ExecuteDataReader(sqlString, false, null);
        }

        /// <summary>
        /// 返回数据只读游标集
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <returns></returns>
        public IDataReader ExecuteDataReader(string sqlString, bool isProcedure)
        {
            return ExecuteDataReader(sqlString, isProcedure, null);
        }

        /// <summary>
        /// 返回数据只读游标集
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="parameters">sql语句对应输入参数</param>
        /// <returns></returns>
        public IDataReader ExecuteDataReader(string sqlString, params IDataParameter[] parameters)
        {
            return ExecuteDataReader(sqlString, false, parameters);
        }

        /// <summary>
        /// 返回数据只读游标集
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <param name="parameters">sql语句对应输入参数</param>
        /// <returns></returns>
        public IDataReader ExecuteDataReader(string sqlString, bool isProcedure, params IDataParameter[] parameters)
        {
            try
            {
                _dbProvider.Connection.Open();
                _dbProvider.Command.CommandText = sqlString;
                _dbProvider.Command.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text;

                _dbProvider.Command.Parameters.Clear();
                if (parameters != null)
                {
                    foreach (IDataParameter param in parameters)
                    {
                        _dbProvider.Command.Parameters.Add(param);
                    }
                }
                IDataReader reader = _dbProvider.Command.ExecuteReader();
                return reader;
            }
            finally
            {
                _dbProvider.Connection.Close();
            }
        }
        #endregion

        #region ExecuteTable 获取数据表结构集合
        /// <summary>
        /// 获取数据表结构集合
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns></returns>
        public DataTable ExecuteTable(string sqlString)
        {
            return ExecuteTable(sqlString, false, null);
        }

        /// <summary>
        ///  获取数据表结构集合
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <returns></returns>
        public DataTable ExecuteTable(string sqlString, bool isProcedure)
        {
            return ExecuteTable(sqlString, isProcedure, null);
        }

        /// <summary>
        /// 获取数据表结构集合
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="parameters">sql语句对应参数</param>
        /// <returns></returns>
        public DataTable ExecuteTable(string sqlString, params IDataParameter[] parameters)
        {
            return ExecuteTable(sqlString, false, parameters);
        }

        /// <summary>
        /// 获取数据表结构集合
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="isProcedure">是否为存储过程</param>
        /// <param name="parameters">sql语句对应参数</param>
        /// <returns></returns>
        public DataTable ExecuteTable(string sqlString, bool isProcedure, params IDataParameter[] parameters)
        {
            try
            {
                _dbProvider.Connection.Open();
                _dbProvider.Command.CommandText = sqlString;
                _dbProvider.Command.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text;

                _dbProvider.Command.Parameters.Clear();
                if (parameters != null)
                {
                    foreach (IDataParameter param in parameters)
                    {
                        _dbProvider.Command.Parameters.Add(param);
                    }
                }
                DataSet ds = new DataSet();
                _dbProvider.Adapter.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                _dbProvider.Connection.Close();
            }
        }
        #endregion

        /// <summary>
        /// 释放内存空间
        /// </summary>
        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }
    }
}
